﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2012/1/5 22:58:30
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Sector7.Restaurant.EntityType;
using Sector7.Framework.Core;
using Sector7.Framework.Utility;
using System.Data.SqlClient;
using Sector7.Restaurant.DAO.Utility;
using Sector7.Framework.DB;

namespace Sector7.Restaurant.DAO.OleDB
{
    /// <summary>
    /// 雇员模块数据访问对象
    /// </summary>
    public class EmployeeDAO : IEmployeeDAO
    {
        #region Public functions
        public long Create(RS_Employee employee)
        {
            using (DBHelper opt = new DBHelper())
            {
                string storedProcedureName = "RP_InsertEmployee";

                SqlParameter uniqueId = DBUtil.CreateDirectionParameter("@ReturnValue", DbType.Int64, ParameterDirection.ReturnValue);
                opt.ExecuteStoredProcedureNonQuery(
                    storedProcedureName,
                    DBUtil.CreateSqlParameter(employee.FirstName, "FirstName"),
                    DBUtil.CreateSqlParameter(employee.LastName, "LastName"),
                    DBUtil.CreateSqlParameter(employee.Birthday, "Birthday"),
                    DBUtil.CreateSqlParameter(employee.UserName, "UserName"),
                    DBUtil.CreateSqlParameter(employee.Password, "Password"),
                    DBUtil.CreateSqlParameter(employee.MobilePhone, "MobilePhone"),
                    DBUtil.CreateSqlParameter(employee.Telephone, "Telephone"),
                    DBUtil.CreateSqlParameter(employee.PersonalId, "PersonalId"),
                    DBUtil.CreateSqlParameter(employee.Gender, "Gender"),
                    DBUtil.CreateSqlParameter(employee.Residence, "Residence"),
                    DBUtil.CreateSqlParameter(employee.Address, "Address"),
                    DBUtil.CreateSqlParameter(employee.GroupId, "GroupId"),
                    DBUtil.CreateSqlParameter(employee.IsValid, "IsValid"),
                    DBUtil.CreateSqlParameter(employee.BasicSalary, "BasicSalary"),
                    uniqueId);

                return Convert.ToInt64(uniqueId.Value);
            }
        }

        public RS_Employee Read(long uniqueId)
        {
            using (DBHelper opt = new DBHelper())
            {
                object employee = null;
                string sql =
                    "SELECT                                         " +
                    "    *                                          " +
                    "FROM                                           " +
                    "    RS_Employee AS employee                    " +
                    "LEFT JOIN                                      " +
                    "    S7_Citizen AS citizen                      " +
                    "ON                                             " +
                    "    employee.UniqueId = citizen.UniqueId       " +
                    "LEFT JOIN                                      " +
                    "   RS_EmployeeGroup AS employeeGroup           " +
                    "ON                                             " +
                    "   employee.GroupId = employeeGroup.GroupId    " +
                    "WHERE                                          " +
                    "    employee.UniqueId = @UniqueId              ";

                SqlDataReader reader = opt.ExecuteQuery(
                    sql,
                    DBUtil.CreateSqlParameter(uniqueId, "UniqueId"));

                if (reader.Read())
                {
                    employee = new RS_Employee();
                    object employeeGroup = new RS_EmployeeGroup();

                    DBUtil.ReadData(reader, ref employee);
                    DBUtil.ReadData(reader, ref employeeGroup);

                    (employee as RS_Employee).Group = employeeGroup as RS_EmployeeGroup;
                }

                return employee as RS_Employee;
            }
        }

        public RS_Employee Read(string userName, string password)
        {
            using (DBHelper opt = new DBHelper())
            {
                object employee = null;
                string sql = @"
                    SELECT *
                        FROM RS_Employee AS employee
                            LEFT JOIN S7_Citizen AS citizen
                                ON employee.UniqueId = citizen.UniqueId
                            LEFT JOIN RS_EmployeeGroup AS employeeGroup
                                ON employee.GroupId = employeeGroup.GroupId
                        WHERE
                            citizen.UserName = @UserName AND
                            citizen.Password = @Password";

                SqlDataReader reader = opt.ExecuteQuery(
                    sql,
                    DBUtil.CreateSqlParameter(userName, "UserName"),
                    DBUtil.CreateSqlParameter(password, "Password"));

                if (reader.Read())
                {
                    employee = new RS_Employee();
                    object employeeGroup = new RS_EmployeeGroup();

                    DBUtil.ReadData(reader, ref employee);
                    DBUtil.ReadData(reader, ref employeeGroup);

                    (employee as RS_Employee).Group = employeeGroup as RS_EmployeeGroup;
                }
                
                return employee as RS_Employee;
            }
        }

        public List<RS_Employee> ReadAll(bool isValid)
        {
            return ReadAll(-1, isValid);
        }

        public List<RS_Employee> ReadAll(int groupId)
        {
            return ReadAll(groupId, true);
        }

        public List<RS_Employee> ReadAll(int groupId, bool isValid)
        {
            using (DBHelper opt = new DBHelper())
            {
                List<RS_Employee> employees = new List<RS_Employee>();
                string sql =
                    "SELECT                                         " +
                    "    *                                          " +
                    "FROM                                           " +
                    "    RS_Employee AS employee                    " +
                    "LEFT JOIN                                      " +
                    "    S7_Citizen AS citizen                      " +
                    "ON                                             " +
                    "    employee.UniqueId = citizen.UniqueId       " +
                    "LEFT JOIN                                      " +
                    "   RS_EmployeeGroup AS employeeGroup           " +
                    "ON                                             " +
                    "   employee.GroupId = employeeGroup.GroupId    " +
                    "WHERE                                          " +
                    "    employee.IsValid = @IsValid                ";

                List<SqlParameter> sqlParams = new List<SqlParameter>();

                if (groupId > 0)
                {
                    sql +=
                        "AND                                        " +
                        "   employee.GroupId = @GroupId             ";

                    sqlParams.Add(DBUtil.CreateSqlParameter(groupId, "GroupId"));
                }
                sqlParams.Add(DBUtil.CreateSqlParameter(isValid, "IsValid"));

                SqlDataReader reader = opt.ExecuteQuery(sql, sqlParams.ToArray());

                while (reader.Read())
                {
                    object employee = new RS_Employee();
                    object employeeGroup = new RS_EmployeeGroup();

                    DBUtil.ReadData(reader, ref employee);
                    DBUtil.ReadData(reader, ref employeeGroup);

                    (employee as RS_Employee).Group = employeeGroup as RS_EmployeeGroup;

                    employees.Add(employee as RS_Employee);
                }

                return employees;
            }
        }

        public int Update(EntityDelta delta)
        {
            int ret = -1;
            string propertyName;
            object propertyValue;

            EntityDelta employeeDelta = new EntityDelta();
            employeeDelta.EntityName = "RS_Employee";
            employeeDelta.Conditions = delta.Conditions;

            EntityDelta citizenDelta = new EntityDelta();
            citizenDelta.EntityName = "S7_Citizen";
            citizenDelta.Conditions = delta.Conditions;

            foreach (var kvp in delta.DeltaProperties)
            {
                propertyName = kvp.Key;
                propertyValue = kvp.Value;
                switch (propertyName)
                {
                    case "GroupId":
                    case "IsValid":
                    case "BasicSalary":
                        employeeDelta.DeltaProperties.Add(propertyName, propertyValue);
                        break;
                    default:
                        citizenDelta.DeltaProperties.Add(propertyName, propertyValue);
                        break;
                }
            }

            using (DBHelper opt = new DBHelper())
            {
                ret = opt.ExecuteNonQuery(
                    DBUtil.CreateUpdateSql(employeeDelta),
                    DBUtil.CreateUpdateParameters(employeeDelta));
            }

            using (DBHelper opt = new DBHelper())
            {
                ret += opt.ExecuteNonQuery(
                    DBUtil.CreateUpdateSql(citizenDelta),
                    DBUtil.CreateUpdateParameters(citizenDelta));
            }

            return ret;
        }

        public int Delete(long uniqueId)
        {
            using (DBHelper opt = new DBHelper())
            {
                string storedProcedureName = "RP_DeleteEmployee";

                return opt.ExecuteStoredProcedureNonQuery(
                    storedProcedureName,
                    DBUtil.CreateSqlParameter(uniqueId, "UniqueId"));
            }
        }
        #endregion
    }
}
